﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient

Public Class TxCanViajes

    Private ejecutor As Ejecutor = Nothing



    Public Function GetMetaCombustible(ByVal idRegionOperativa As String, ByVal idRegion As String, _
                                       ByVal mes As String, ByVal anio As String) As String
        Dim strMeta As String = String.Empty
        ejecutor = New Ejecutor
        Dim strConsulta As String = String.Empty
        strConsulta = "Select MRE_Meta From CAT_Metas_Rendimiento_Regiones Where IDRegionOperativa = " _
        & idRegionOperativa & "  And IDRegion = " & idRegion & " And  MRE_Mes = " & mes & " And MRE_Año = " & anio & " "
        strMeta = ejecutor.consultaEscalarCadena(strConsulta)

        Return strMeta

    End Function

    Public Function GetFactorAmarillo() As String
        Dim strRes As String = String.Empty
        ejecutor = New Ejecutor
        Dim strConsulta As String = String.Empty
        strConsulta = "Select Valor FROM CatParametros Where IDParam='PorcentajeAmarillos' "
        strRes = ejecutor.consultaEscalarCadena(strConsulta)

        Return strRes

    End Function


    Public Function GetFactorAmarillo(ByVal idRegionOperativa As String, ByVal idRegion As String) As String
        Dim strRes As String = String.Empty
        ejecutor = New Ejecutor
        Dim strConsulta As String = String.Empty
        strConsulta = "Select Valor FROM CatParametros Where IDParam='PorcentajeAmarillos' And IDRegionOperativa = " & idRegionOperativa & " And IDRegion = " & idRegion
        strRes = ejecutor.consultaEscalarCadena(strConsulta)

        Return strRes

    End Function


    Public Function GetSemaforoAutobuses(ByVal strTipo As String, ByVal dteFechaInicial As DateTime, ByVal dteFechaFinal As DateTime, _
                                         ByVal idRegionOperativa As String, ByVal idRegion As String, ByVal strMetaMes As String, ByVal strFactorAmarillo As String) As List(Of SemaforoAutobus)
        ejecutor = New Ejecutor
        Dim strConsulta As String = String.Empty
        Dim sqlComando As Data.SqlClient.SqlCommand
        Dim ds As New Data.DataSet
        Dim lista As New List(Of SemaforoAutobus)

        If strTipo.Equals("factorRendimiento") = True Or strTipo.Equals("conductor") = True Then
            If strMetaMes.Length <= 0 Then
                strMetaMes = "0.00"
            End If

            If strFactorAmarillo.Length <= 0 Then
                strFactorAmarillo = "0.00"
            End If

            If strTipo.Equals("factorRendimiento") = True Then
                strConsulta = "SELECT  a.Autobus, SUM( round(a.LitrosCons,2) ) AS Litros, SUM( round(a.KmsRec,2) ) AS Kilometros, " & strMetaMes & " as Meta, " & strFactorAmarillo & " as FactorAmarillo  , CASE WHEN SUM(a.LitrosCons) = 0 THEN 0 ELSE Round(isnull(isnull(sum(round(A.kmsrec,2)),2) / isnull(sum(round(A.litroscons,2)),1),0),2) END AS FactorRendimiento "
                strConsulta += " FROM CANViajes as A inner join can_parametrosinicio as B "
                strConsulta += " on ( cast(a.autobus as integer) = cast(b.autobus as integer) and A.IdRegion=B.Region and A.IdRegionOperativa =B.IdRegionOperativa and  b.IDRegionOperativa = @IDRegionOperativa And b.Region = @Region ) "
                strConsulta += " WHERE  A.FechaViaje between @FechaInicial  AND  @FechaFinal "
                strConsulta += " GROUP BY A.Autobus ORDER BY FactorRendimiento DESC"
            ElseIf strTipo.Equals("conductor") = True Then

                strConsulta = "SELECT  A.Autobus, SUM( round(A.LitrosCons,2) ) AS Litros, SUM( round(A.KmsRec,2) ) AS Kilometros, " & strMetaMes & " as Meta ," & strFactorAmarillo & " as FactorAmarillo, CASE WHEN SUM(A.LitrosCons) = 0 THEN 0 ELSE Round(isnull(isnull(sum(round(A.kmsrec,2)),2) / isnull(sum(round(A.litroscons,2)),1),0),2) END AS FactorRendimiento "
                strConsulta += " FROM CANViajes as A inner join can_parametrosinicio as B "
                strConsulta += " on ( Cast(a.autobus as integer) = Cast(b.autobus as integer) and A.IdRegion=B.Region and A.IdRegionOperativa =B.IdRegionOperativa and b.IDRegionOperativa = @IDRegionOperativa And b.Region = @Region ) "
                strConsulta += " WHERE  A.FechaViaje between @FechaInicial  AND  @FechaFinal "
                strConsulta += " GROUP BY A.Autobus ORDER BY Cast(A.Autobus as integer) ASC "
            End If

            sqlComando = New SqlCommand()
            sqlComando.Parameters.AddWithValue("@Region", idRegion)
            sqlComando.Parameters.AddWithValue("@IDRegionOperativa", idRegionOperativa)
            sqlComando.Parameters.AddWithValue("@FechaInicial", dteFechaInicial)
            sqlComando.Parameters.AddWithValue("@FechaFinal", dteFechaFinal)
            sqlComando.CommandText = strConsulta
            ds = ejecutor.consultaSQL_ConParametros(sqlComando)
            Dim drlist As List(Of Data.DataRow) = Utilerias.convertirDatasetToListDatarow(ds)
            Dim semaforo As SemaforoAutobus = Nothing
            For Each row As Data.DataRow In drlist
                semaforo = New SemaforoAutobus
                With semaforo
                    .Autobus = row("Autobus").ToString
                    .Litros = row("Litros").ToString
                    .Kilometros = row("Kilometros").ToString
                    .Meta = row("Meta").ToString
                    .FactorRendimiento = row("FactorRendimiento").ToString
                    .FactorAmarillo = row("FactorAmarillo").ToString

                End With
                lista.Add(semaforo)
            Next

        End If

        Return lista

    End Function


    Public Function GetSemaforoConductores(ByVal strTipo As String, ByVal dteFechaInicial As DateTime, ByVal dteFechaFinal As DateTime, _
                                     ByVal idRegionOperativa As String, ByVal idRegion As String, ByVal strMetaMes As String, ByVal strFactorAmarillo As String) As List(Of SemaforoConductor)

        ejecutor = New Ejecutor
        Dim strConsulta As String = String.Empty
        Dim sqlComando As Data.SqlClient.SqlCommand
        Dim ds As New Data.DataSet
        Dim lista As New List(Of SemaforoConductor)

        If strTipo.Equals("factorRendimiento") = True Or strTipo.Equals("conductor") = True Then
            If strMetaMes.Length <= 0 Then
                strMetaMes = "0.00"
            End If

            If strFactorAmarillo.Length <= 0 Then
                strFactorAmarillo = "0.00"
            End If

            strConsulta = "SELECT A.Conductor,  C.nombre, SUM( Round(A.LitrosCons,2) ) AS Litros, "
            strConsulta += " SUM( Round(A.KmsRec,2) ) AS Kilometros," & strMetaMes & " as Meta, " & strFactorAmarillo & " as FactorAmarillo,"
            strConsulta += " CASE WHEN SUM(A.LitrosCons) = 0 THEN 0 ELSE Round(isnull(isnull(sum(round(A.kmsrec,2)),2) / isnull(sum(round(A.litroscons,2)),1),0),2) END AS FactorRendimiento "
            strConsulta += " FROM canviajes as A LEFT OUTER JOIN DetalleOperador as B on "
            strConsulta += " (A.Conductor=B.ClaveEmpleado and A.IDRegion = B.Region and A.IDRegionOperativa=B.DireccionOperativa and B.DireccionOperativa = @IDRegionOperativa And B.Region = @Region ) "
            strConsulta += " inner join operadores  as C on "
            strConsulta += " (C.cveemp= B.ClaveEmpleado) "
            strConsulta += " WHERE A.FechaViaje BETWEEN @FechaInicial  AND @FechaFinal "
            strConsulta += " GROUP BY A.Conductor, C.nombre  "

            If strTipo.Equals("factorRendimiento") = True Then
                strConsulta &= "ORDER BY FactorRendimiento DESC"
            ElseIf strTipo.Equals("conductor") = True Then
                strConsulta &= "ORDER BY C.nombre ASC"
            End If

            sqlComando = New SqlCommand()
            sqlComando.Parameters.AddWithValue("@Region", idRegion)
            sqlComando.Parameters.AddWithValue("@IDRegionOperativa", idRegionOperativa)
            sqlComando.Parameters.AddWithValue("@FechaInicial", dteFechaInicial)
            sqlComando.Parameters.AddWithValue("@FechaFinal", dteFechaFinal)
            sqlComando.CommandText = strConsulta
            ds = ejecutor.consultaSQL_ConParametros(sqlComando)
            Dim drlist As List(Of Data.DataRow) = Utilerias.convertirDatasetToListDatarow(ds)
            Dim semaforo As SemaforoConductor = Nothing
            For Each row As Data.DataRow In drlist
                semaforo = New SemaforoConductor
                With semaforo
                    .Conductor = row("Conductor").ToString
                    .Nombre = row("Nombre").ToString
                    .Litros = row("Litros").ToString
                    .Kilometros = row("Kilometros").ToString
                    .Meta = row("Meta").ToString
                    .FactorRendimiento = row("FactorRendimiento").ToString
                    .FactorAmarillo = row("FactorAmarillo").ToString

                End With
                lista.Add(semaforo)
            Next

        End If

        Return lista

    End Function


    Public Function GetSemaforoAutobuses() As List(Of SemaforoAutobus)
        Dim lista As New List(Of SemaforoAutobus)
        Dim semaforo As New SemaforoAutobus
        With semaforo
            .Autobus = "50"
            .Litros = "120"
            .Kilometros = "169.56"
            .Meta = "10"
            .FactorRendimiento = "56.5"
        End With
        lista.Add(semaforo)

        Return lista

    End Function



    'Private Sub Visualizar_Rendimiento_Autobuses()
    '    Dim MyConsulta As String
    '    Dim UltimaFecha As Date
    '    Dim TmpVerde As Int16
    '    Dim TmpAmarillo As Int16
    '    Dim TmpRojo As Int16
    '    Dim CuantosVerdes As Integer = 0
    '    Dim CuantosAmarillos As Integer = 0
    '    Dim CuantosRojos As Integer = 0
    '    Try
    '        'Pregunto si se puede realizar la conexion a la base de datos

    '        Dim MetaCombustible As Double
    '        Dim FactorAmarillo As Double


    '        'Obtener la META DEL MES / AÑO
    '        'Creo mi variable que contendra mi consulta
    '        MyConsulta = "Select IDRegionOperativa,IDRegion,MRE_Año,MRE_Mes,MRE_Meta,MRE_Gestion,MRE_Status From CAT_Metas_Rendimiento_Regiones Where IDRegionOperativa = " _
    '     & Me.Valores.ValorIdDireccionweb(Me) & "  And IDRegion = " & Me.Valores.ValorIdRegionWeb(Me) & " And  MRE_Mes = " & Cmb_Mes.SelectedValue & " And MRE_Año = " _
    '     & Cmb_Año.SelectedValue & " "


    '        Dim Mycommand As SqlClient.SqlCommand
    '        Dim MyReader As SqlClient.SqlDataReader
    '        Mycommand = New SqlClient.SqlCommand(MyConsulta, ConnectionSql)
    '        Mycommand.CommandTimeout = 400
    '        MyReader = Mycommand.ExecuteReader

    '        If MyReader.Read Then
    '            MetaCombustible = MyReader("MRE_Meta")
    '        Else
    '            MetaCombustible = 0
    '        End If
    '        MyReader.Close()
    '        Mycommand = Nothing
    '        MyReader = Nothing
    '        MyConsulta = ""


    '        'Sacar el factor para los amarillos
    '        'Creo mi variable que contendra mi consulta
    '        MyConsulta = "Select Valor FROM CatParametros Where  IDParam='" & "PorcentajeAmarillos" & "' " 'IDRegionOperativa = " & IdDireccionWeb & " And IDRegion = " & IdRegionWeb & " And


    '        Mycommand = New SqlClient.SqlCommand(MyConsulta, ConnectionSql)
    '        Mycommand.CommandTimeout = 400
    '        MyReader = Mycommand.ExecuteReader

    '        If MyReader.Read Then
    '            FactorAmarillo = (MyReader("Valor") / 100)
    '        Else
    '            FactorAmarillo = 0
    '        End If
    '        MyReader.Close()
    '        MyConsulta = ""

    '        'Meta del Mes y Año seleccinado

    '        Lbl.Text = "Meta del Mes de " & Cmb_Mes.SelectedItem.Text & "-" & Cmb_Año.SelectedValue & " : " & MetaCombustible.ToString("##.00")

    '        'Verde
    '        Lbl_Verde.Text = "Factor Rendimiento >= " & Format(MetaCombustible, "##.##")

    '        ValorVerde = MetaCombustible
    '        ValorAmarillo1 = (MetaCombustible - 0.01)
    '        ValorAmarillo2 = (MetaCombustible - (MetaCombustible * FactorAmarillo))
    '        ValorRojo = ((MetaCombustible - (MetaCombustible * FactorAmarillo)) - 0.01)
    '        ValorRojo = Math.Round(ValorRojo, 2)
    '        'Amarillo
    '        Lbl_Amarillo.Text = "Factor Rendimiento Entre " & Format((MetaCombustible - 0.01), "##.##") & " y " & Format((MetaCombustible - (MetaCombustible * FactorAmarillo)), "##.##")

    '        'Rojo
    '        Lbl_Rojo.Text = "Factor Rendimiento <= " & Format(((MetaCombustible - (MetaCombustible * FactorAmarillo)) - 0.01), "##.##")

    '        'Desplegando Informacion de Semaforo de acuerdo al tipo de selecciono Rendimiento o Conductor
    '        Dim MyDataAdapter As SqlClient.SqlDataAdapter
    '        Dim MyDataset As DataSet = New DataSet
    '        If Cmb_Orden.SelectedValue = 1 Then
    '            Dim anio As String = Cmb_Año.SelectedValue.ToString
    '            Dim messtring As String = Cmb_Mes.SelectedValue.ToString
    '            Dim fecha As String = anio & "-" & messtring & "-01T00:00:00"
    '            Dim fecha2 As String = fechaFinalMensual(Cmb_Mes.SelectedValue.ToString, Cmb_Año.SelectedValue.ToString)

    '            MyConsulta = "SELECT a.Autobus, SUM( round(a.LitrosCons,2) ) AS Litros, SUM( round(a.KmsRec,2) ) AS Kilometros, " & MetaCombustible & " as Meta , CASE WHEN SUM(a.LitrosCons) = 0 THEN 0 ELSE Round(isnull(isnull(sum(round(A.kmsrec,2)),2) / isnull(sum(round(A.litroscons,2)),1),0),2) END AS FactorRendimiento "
    '            MyConsulta += " FROM CANViajes as A inner join can_parametrosinicio as B "
    '            MyConsulta += String.Format(" on ( cast(a.autobus as integer) = cast(b.autobus as integer) and A.IdRegion=B.Region and A.IdRegionOperativa =B.IdRegionOperativa and  b.IDRegionOperativa = {0} And b.Region ={1} ) ", Me.Valores.ValorIdDireccionweb(Me), Me.Valores.ValorIdRegionWeb(Me))
    '            MyConsulta += String.Format(" WHERE  A.FechaViaje between '{0}'  AND  '{1}' ", fecha, fecha2)
    '            MyConsulta += " GROUP BY A.Autobus ORDER BY FactorRendimiento DESC"
    '        Else
    '            Dim anio As String = Cmb_Año.SelectedValue.ToString
    '            Dim messtring As String = Cmb_Mes.SelectedValue.ToString
    '            Dim fecha As String = anio & "-" & messtring & "-01T00:00:00"
    '            Dim fecha2 As String = fechaFinalMensual(Cmb_Mes.SelectedValue.ToString, Cmb_Año.SelectedValue.ToString)

    '            MyConsulta = "SELECT A.Autobus, SUM( round(A.LitrosCons,2) ) AS Litros, SUM( round(A.KmsRec,2) ) AS Kilometros, " & MetaCombustible & " as Meta , CASE WHEN SUM(A.LitrosCons) = 0 THEN 0 ELSE Round(isnull(isnull(sum(round(A.kmsrec,2)),2) / isnull(sum(round(A.litroscons,2)),1),0),2) END AS FactorRendimiento "
    '            MyConsulta += " FROM CANViajes as A inner join can_parametrosinicio as B "
    '            MyConsulta += String.Format(" on ( Cast(a.autobus as integer) = Cast(b.autobus as integer) and A.IdRegion=B.Region and A.IdRegionOperativa =B.IdRegionOperativa and b.IDRegionOperativa = {0} And b.Region ={1} ) ", Me.Valores.ValorIdDireccionweb(Me), Me.Valores.ValorIdRegionWeb(Me))
    '            MyConsulta += String.Format(" WHERE  A.FechaViaje between '{0}'  AND  '{1}' ", fecha, fecha2)
    '            MyConsulta += " GROUP BY A.Autobus ORDER BY Cast(A.Autobus as integer) ASC "
    '        End If


    '        MyDataAdapter = New SqlClient.SqlDataAdapter(MyConsulta, ConnectionSql)
    '        MyDataAdapter.SelectCommand.CommandTimeout = 400
    '        MyDataAdapter.Fill(MyDataset, "Rendimiento") 'FILL   llena el dataSet con la consulta realizada

    '        Dgv_Rendimiento.DataSource = (MyDataset.Tables("Rendimiento"))
    '        Dgv_Rendimiento.DataBind()
    '        If Dgv_Rendimiento.Items.Count = 0 Then
    '            ShowAlert("NO HAY DATOS QUE MOSTRAR PARA ESTA CONSULTA")
    '            Exit Sub

    '        End If
    '        Dim dt As New DataTable
    '        DataGridCabecera.DataSource = dt
    '        DataGridCabecera.DataBind()

    '        'If Dgv_Rendimiento.Items.Count = 0 Then Dgv_Rendimiento.Caption = "NO EXISTEN REGISTROS" Else Dgv_Rendimiento.Caption = ""

    '        'modificado por FVO ya que envia error
    '        ' se reemplazo por ShowAlert para mostrar un mensaje de que no existen datos
    '        ' If Dgv_Versiones.Items.Count = 0 Then Dgv_Versiones.Caption = "NO EXISTEN REGISTROS" Else Dgv_Versiones.Caption = ""



    '        TmpVerde = CInt(((CuantosVerdes) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100))
    '        TmpAmarillo = CInt(((CuantosAmarillos) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100))
    '        TmpRojo = (100 - (TmpVerde + TmpAmarillo))

    '        'LblVerde.Text = Format(((CuantosVerdes) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100), "00") & " % (" & CuantosVerdes & ")"
    '        'LblAmarillo.Text = Format(((CuantosAmarillos) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100), "00") & " % (" & CuantosAmarillos & ")"
    '        'LblRojo.Text = Format(((CuantosRojos) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100), "00") & " % (" & CuantosRojos & ")"

    '        LblVerde.Text = Format(TmpVerde, "00") & " % (" & CuantosVerdes & ")"
    '        LblAmarillo.Text = Format(TmpAmarillo, "00") & " % (" & CuantosAmarillos & ")"
    '        LblRojo.Text = Format(TmpRojo, "00") & " % (" & CuantosRojos & ")"

    '        Else

    '        'Envio mensaje de aviso de no conexion
    '        ShowAlert("Imposible realizar conexion con el servidor.")


    '        'Pregunto si la conexion esta abierta de ser asi la cierro.
    '        If ConnectionSql.State = ConnectionState.Open Then ConnectionSql.Close()

    '    Catch ex As Exception

    '        ShowAlert(Err.Description)
    '    End Try
    'End Sub









    'Enlista_Rendimiento_Conductores


    'Private Sub Visualizar_Rendimiento_Conductores()
    '    Dim MyConsulta As String
    '    Dim UltimaFecha As Date

    '    Dim TmpVerde As Int16
    '    Dim TmpAmarillo As Int16
    '    Dim TmpRojo As Int16

    '    Try
    '        'Pregunto si se puede realizar la conexion a la base de datos
    '        If Conectar_a_BaseDatosSQL() = True Then

    '            Dim MetaCombustible As Double
    '            Dim FactorAmarillo As Double

    '            CuantosVerdes = 0
    '            CuantosAmarillos = 0
    '            CuantosRojos = 0

    '            'Obtener la META DEL MES / AÑO
    '            'Creo mi variable que contendra mi consulta
    '            MyConsulta = "Select MRE_Meta From CAT_Metas_Rendimiento_Regiones Where IDRegionOperativa = " & Me.Valores.ValorIdDireccionweb(Me) & " And IDRegion = " & Me.Valores.ValorIdRegionWeb(Me) & "  And MRE_Mes = " & Cmb_Mes.SelectedValue & " And MRE_Año = " & Cmb_Año.SelectedValue & " "
    '            Dim Mycommand As SqlClient.SqlCommand
    '            Dim MyReader As SqlClient.SqlDataReader
    '            Mycommand = New SqlClient.SqlCommand(MyConsulta, ConnectionSql)
    '            Mycommand.CommandTimeout = 400
    '            MyReader = Mycommand.ExecuteReader

    '            If MyReader.Read Then
    '                MetaCombustible = MyReader("MRE_Meta")
    '            Else
    '                MetaCombustible = 0
    '            End If
    '            MyReader.Close()
    '            Mycommand = Nothing
    '            MyReader = Nothing
    '            MyConsulta = ""


    '            'Sacar el factor para los amarillos
    '            'Creo mi variable que contendra mi consulta
    '            MyConsulta = "Select Valor FROM CatParametros Where IDRegionOperativa = " & Me.Valores.ValorIdDireccionweb(Me) & " And IDRegion = " & Me.Valores.ValorIdRegionWeb(Me) & " And IDParam='" & "PorcentajeAmarillos" & "' "
    '            Mycommand = New SqlClient.SqlCommand(MyConsulta, ConnectionSql)
    '            Mycommand.CommandTimeout = 400

    '            Try
    '                MyReader = Mycommand.ExecuteReader
    '                If MyReader.Read Then
    '                    FactorAmarillo = (MyReader("Valor") / 100)
    '                Else
    '                    FactorAmarillo = 0
    '                End If
    '                MyReader.Close()
    '            Catch ex As Exception
    '                FactorAmarillo = 0
    '            Finally
    '                MyReader = Nothing
    '                MyConsulta = ""
    '            End Try

    '            'Meta del Mes y Año seleccinado
    '            Lbl.Text = "Meta del Mes de " & Cmb_Mes.SelectedItem.Text & "-" & Cmb_Año.SelectedValue & " : " & MetaCombustible.ToString("##.00")

    '            'Verde
    '            Lbl_Verde.Text = "Factor Rendimiento >= " & Format(MetaCombustible, "##.##")

    '            ValorVerde = MetaCombustible
    '            ValorAmarillo1 = (MetaCombustible - 0.01)
    '            ValorAmarillo2 = (MetaCombustible - (MetaCombustible * FactorAmarillo))
    '            ValorRojo = ((MetaCombustible - (MetaCombustible * FactorAmarillo)) - 0.01)
    '            ValorRojo = Math.Round(ValorRojo, 2)

    '            'Amarillo
    '            Lbl_Amarillo.Text = "Factor Rendimiento Entre " & Format((MetaCombustible - 0.01), "##.##") & " y " & Format((MetaCombustible - (MetaCombustible * FactorAmarillo)), "##.##")

    '            'Rojo
    '            Lbl_Rojo.Text = "Factor Rendimiento <= " & Format(((MetaCombustible - (MetaCombustible * FactorAmarillo)) - 0.01), "##.##")

    '            Session("valorVerde") = ValorVerde
    '            Session("valorAmarillo1") = ValorAmarillo1
    '            Session("valorAmarillo2") = ValorAmarillo2
    '            Session("valorRojo") = ValorRojo

    '            'Desplegando Informacion de Semaforo de acuerdo al tipo de selecciono Rendimiento o Conductor
    '            Dim MyDataAdapter As SqlClient.SqlDataAdapter
    '            Me.MyDataset = New DataSet

    '            'Vieja Version no toma en cuanta los conductores con la tabla de Detalle de Conductores.
    '            'MyConsulta = "SELECT CANViajes.Conductor,  operadores.nombre, "
    '            'MyConsulta &= "SUM(CANViajes.LitrosCons) AS Litros, ROUND(SUM(CANViajes.KmsRec),0,1) "
    '            'MyConsulta &= "AS Kilometros, " & MetaCombustible & " as Meta , "
    '            'MyConsulta &= "CASE WHEN SUM(LitrosCons) = 0 THEN 0 "
    '            'MyConsulta &= "ELSE ROUND(SUM(KmsRec) / SUM(LitrosCons),2,1) END AS FactorRendimiento "
    '            'MyConsulta &= "FROM CANViajes LEFT OUTER JOIN operadores "
    '            'MyConsulta &= "ON CANViajes.Conductor = operadores.cveemp "
    '            'MyConsulta &= "WHERE IDRegion = " & IdRegionWeb & " "
    '            'MyConsulta &= "And CANViajes.FechaViaje BETWEEN " & fechaInicialMensual(Cmb_Mes.SelectedValue, Cmb_Año.SelectedValue) & " "
    '            'MyConsulta &= "And " & fechaFinalMensual(Cmb_Mes.SelectedValue, Cmb_Año.SelectedValue) & " "
    '            'MyConsulta &= "GROUP BY CANViajes.Conductor, operadores.nombre "

    '            MyConsulta = "SELECT A.Conductor,  C.nombre, SUM( Round(A.LitrosCons,2) ) AS Litros, "
    '            MyConsulta += " SUM( Round(A.KmsRec,2) ) AS Kilometros," & MetaCombustible & " as Meta , "
    '            MyConsulta += " CASE WHEN SUM(A.LitrosCons) = 0 THEN 0 ELSE Round(isnull(isnull(sum(round(A.kmsrec,2)),2) / isnull(sum(round(A.litroscons,2)),1),0),2) END AS FactorRendimiento "
    '            MyConsulta += " FROM canviajes as A LEFT OUTER JOIN DetalleOperador as B on "
    '            MyConsulta += String.Format(" (A.Conductor=B.ClaveEmpleado and A.IDRegion = B.Region and A.IDRegionOperativa=B.DireccionOperativa and B.DireccionOperativa={1} and B.Region={0}) ", Me.Valores.ValorIdRegionWeb(Me), Me.Valores.ValorIdDireccionweb(Me))
    '            MyConsulta += " inner join operadores  as C on "
    '            MyConsulta += " (C.cveemp= B.ClaveEmpleado) "
    '            MyConsulta += String.Format(" WHERE A.FechaViaje BETWEEN {0} And {1} ", fechaInicialMensual(Cmb_Mes.SelectedValue, Cmb_Año.SelectedValue), fechaFinalMensual(Cmb_Mes.SelectedValue, Cmb_Año.SelectedValue))
    '            MyConsulta += " GROUP BY A.Conductor, C.nombre  "

    '            If Cmb_Orden.SelectedValue = 1 Then
    '                MyConsulta &= "ORDER BY FactorRendimiento DESC"
    '            Else
    '                MyConsulta &= "ORDER BY C.nombre ASC"
    '            End If

    '            Try
    '                MyDataAdapter = New SqlClient.SqlDataAdapter(MyConsulta, ConnectionSql)
    '                MyDataAdapter.SelectCommand.CommandTimeout = 400
    '                MyDataAdapter.Fill(MyDataset, "Rendimiento") 'FILL   llena el dataSet con la consulta realizada
    '            Catch ex As Exception
    '                ShowAlert("Aqui  : " & Err.Description)
    '            End Try

    '            If Not MyDataset Is Nothing Then
    '                If MyDataset.Tables.Count > 0 Then
    '                    If MyDataset.Tables("Rendimiento").Rows.Count > 0 Then
    '                        Dgv_Rendimiento.Visible = True
    '                        DataGridCabecera.Visible = True
    '                        Session("datasetRendimiento") = MyDataset
    '                        Dgv_Rendimiento.DataSource = (MyDataset.Tables("Rendimiento"))
    '                        Dgv_Rendimiento.DataBind()
    '                        Dim dt As New DataTable
    '                        DataGridCabecera.DataSource = dt
    '                        DataGridCabecera.DataBind()
    '                        For Each renglon As DataRow In MyDataset.Tables("Rendimiento").Rows
    '                            Dim strRenglonRendimiento As String = CType(renglon.Item("FactorRendimiento"), String)

    '                            If IsNumeric(strRenglonRendimiento.Replace(",", "")) Then
    '                                If (Val(strRenglonRendimiento.Replace(",", ""))) >= ValorVerde Then
    '                                    CuantosVerdes = CuantosVerdes + 1
    '                                End If

    '                                If ((Val(strRenglonRendimiento.Replace(",", ""))) <= ValorAmarillo1) And ((Val(strRenglonRendimiento.Replace(",", ""))) >= ValorAmarillo2) Then
    '                                    CuantosAmarillos = CuantosAmarillos + 1
    '                                End If

    '                                If (Val(strRenglonRendimiento.Replace(",", ""))) <= ValorRojo Then
    '                                    CuantosRojos = CuantosRojos + 1
    '                                End If
    '                            End If
    '                        Next

    '                    Else
    '                        Dgv_Rendimiento.Visible = False
    '                        DataGridCabecera.Visible = False
    '                        Dim dt As New DataTable
    '                        Dgv_Rendimiento.DataSource = dt
    '                        Dgv_Rendimiento.DataBind()
    '                        DataGridCabecera.DataSource = dt
    '                        DataGridCabecera.DataBind()
    '                        ShowAlert("NO EXISTEN REGISTROS")
    '                    End If
    '                End If

    '            End If




    '            'modificado por FVO ya que envia error
    '            ' se reemplazo por ShowAlert para mostrar un mensaje de que no existen datos
    '            ' If Dgv_Versiones.Items.Count = 0 Then Dgv_Versiones.Caption = "NO EXISTEN REGISTROS" Else Dgv_Versiones.Caption = ""

    '            If CuantosVerdes > 0 Then TmpVerde = CInt((CuantosVerdes) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100)

    '            If CuantosAmarillos > 0 Then TmpAmarillo = CInt(((CuantosAmarillos) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100))
    '            TmpRojo = IIf((TmpVerde + TmpAmarillo = 0), 0, (100 - (TmpVerde + TmpAmarillo)))
    '            'TmpVerde = CInt((CuantosVerdes) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100)
    '            'TmpAmarillo = CInt(((CuantosAmarillos) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100))
    '            ' TmpRojo = (100 - (TmpVerde + TmpAmarillo))


    '            'LblVerde.Text = Format(((CuantosVerdes) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100), "00") & " % (" & CuantosVerdes & ")"
    '            'LblAmarillo.Text = Format(((CuantosAmarillos) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100), "00") & " % (" & CuantosAmarillos & ")"
    '            'LblRojo.Text = Format(((CuantosRojos) / (CuantosVerdes + CuantosAmarillos + CuantosRojos) * 100), "00") & " % (" & CuantosRojos & ")"

    '            LblVerde.Text = Format(TmpVerde, "00") & " % (" & CuantosVerdes & ")"
    '            LblAmarillo.Text = Format(TmpAmarillo, "00") & " % (" & CuantosAmarillos & ")"
    '            LblRojo.Text = Format(TmpRojo, "00") & " % (" & CuantosRojos & ")"




    '        Else

    '            'Envio mensaje de aviso de no conexion
    '            ShowAlert("Imposible realizar conexion con el servidor.")

    '        End If

    '        'Pregunto si la conexion esta abierta de ser asi la cierro.
    '        If ConnectionSql.State = ConnectionState.Open Then ConnectionSql.Close()

    '    Catch ex As Exception

    '        ShowAlert(Err.Description)
    '    End Try
    'End Sub







End Class
